Logical operators are symbols or keywords used in programming languages, including SQL, to combine conditions or expressions in logical statements. They help to control the flow of logic and are primarily used to make decisions based on multiple conditions.

Logical operator AND OR NOT in SQL


Also read : can you use WHERE and HAVING clause together 

 In SQL, there are three main logical operators:

AND operator:

This operator combines multiple conditions, and all of them must be true for a row to be selected. It's like saying "both conditions must be satisfied." For example, if you want to find employees who are both in the "IT" department and have more than 5 years of experience, you'd use the AND operator to ensure that both conditions are met.

SELECT * FROM table_name

 WHERE condition1 AND condition2;

Example:

 Imagine you have a database of employees, and you want to find employees who are both in the Sales department and have a salary greater than $50,000. The SQL query using "AND" would look like this:

SELECT * FROM employees
WHERE department = 'Sales' AND salary > 50000;

This query will retrieve only those employees who meet both conditions (in the Sales department and salary greater than $50,000)

Also read : update statement 

OR operator: 

With the OR operator, only one of the conditions needs to be true for a row to be selected. It's like saying "at least one of the conditions must be satisfied." For instance, if you want to find products that are either "on sale" or have a "high rating," you'd use the OR operator.

SELECT * FROM table_name 

WHERE condition1 OR condition2;

Example: 

Suppose you want to find employees who are either in the Sales department or have a salary greater than $50,000. The SQL query using "OR" would be:

SELECT * FROM employees
WHERE department = 'Sales' OR salary > 50000;

This query will retrieve employees who meet either of the conditions (in the Sales department or salary greater than $50,000).

Also read: Question Answer on logical operator AND OR NOT 

NOT operator: 

The NOT operator negates a condition, meaning it selects rows that don't meet the specified condition. It's like saying "exclude rows that meet this condition." For example, if you want to find customers who have not made a purchase in the last 30 days, you'd use the NOT operator to exclude those who do meet that condition.

SELECT * FROM table_name

 WHERE NOT condition;

Example: 

Let's say you want to find employees who are not in the Sales department. The SQL query using "NOT" would be:

SELECT * FROM employees
WHERE NOT department = 'Sales';

This query will retrieve employees who are in departments other than Sales.

Example with combination of AND OR NOT:

 You have a database of movies, and you want to find movies that meet the following criteria:

  • Released after the year 2010.
  • Either directed by the director "Rajesh Gupta" or "Priya Sharma."
  • Not in the genre "Horror."

The SQL query would look like this:

SELECT * FROM movies
WHERE release_year > 2010
  AND (director = 'Rajesh Gupta' OR director = 'Priya Sharma')
  AND NOT genre = 'Horror';

In this query:

The "AND" operator combines the condition for the release year (released after 2010) with the two conditions for directors (Rajesh Gupta or Priya Sharma).

The "OR" operator combines the two director conditions, so if a movie is directed by either Rajesh Gupta or Priya Sharma, it will satisfy this part of the query.

The "NOT" operator negates the condition for the genre "Horror," ensuring that movies in this genre are excluded from the results.

This query will retrieve movies that meet all three conditions:

 released after 2010, directed by either Rajesh Gupta or Priya Sharma, and not categorized as Horror.

Conclusion:

Logical operators are widely used for filtering data in queries, making complex selections, and controlling the flow of conditional statements in programming. They are essential for writing flexible and powerful SQL queries to retrieve specific information from a database.